﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection.Emit;
using System.Text;
using System.Threading.Tasks;
using ModelLayer;
/// <summary>
/// Dataaccess class for Seats
/// </summary>
namespace DBLayer
{
    public class SeatDB
    {
        //Variable initialization
        private SqlConnection con;
        private SqlUtility sqlUtility;
        public SeatDB()
        {
            sqlUtility = new SqlUtility();
        }
        //build object Seat from data from database
        private Seat BuildSeat(SqlDataReader _reader)
        {
            Seat tempSeat = new Seat();
            tempSeat.SeatNo = (string) _reader["seatNo"];
            tempSeat.PlaneNo = (string) _reader["planeNo"];
            return tempSeat;
        }
        //Insert Seat in database by object Seat
        public int InsertSeat(Seat _seat)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "INSERT INTO Seat (seatNo, planeNo) VALUES (@seatNo, @planeNo)";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@seatNo", _seat.SeatNo);
            cmd.Parameters.AddWithValue("@planeNo", _seat.PlaneNo);

            return sqlUtility.ExecuteData(cmd,connect);
        }
        //remove seat in database by object 
        public int RemoveSeat(Seat _seat)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            string query = "DELETE from Seat WHERE seatNo = @seatNo and planeNo = @planeNo";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@seatNo", _seat.SeatNo);
            cmd.Parameters.AddWithValue("@planeNo", _seat.PlaneNo);

            return sqlUtility.ExecuteData(cmd, connect);
        }

        //find Seat in database by string (seatNo) 
        public Seat FindSeatBySeatNoAndPlaneNo(string _seatNo,string _planeNo)
        {
            Seat retSeat = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Seat WHERE seatNo = @seatNo AND planeNo = @planeNo";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@seatNo", _seatNo);
                    cmd.Parameters.AddWithValue("@planeNo", _planeNo);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            retSeat = BuildSeat(rdr);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }

                con.Close();
                con.Dispose();
            }
            return retSeat;
        }
        //Find seats in a plane
        public List<Seat> FindSeatsByPlane(Plane _plane)
        {
            List<Seat> seatList = new List<Seat>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * FROM Seat WHERE planeNo = @planeNo";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@planeNo", _plane.PlaneNo);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Seat tmp = new Seat();
                            tmp = BuildSeat(rdr);
                            seatList.Add(tmp);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return seatList;
        }
        public List<Seat> FindFreeSeatsByFlightInstance(FlightInstance _flightInstance)
        {
            List<Seat> seatList = new List<Seat>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = string.Format("SELECT * FROM Seat WHERE planeNo = @planeNo and NOT EXISTS (SELECT seatNo, planeNo FROM seatbooking WHERE flightInstanceId = @flightInstanceId and seat.seatNo = seatbooking.seatNo)");
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@planeNo", _flightInstance.Plane.PlaneNo);
                    cmd.Parameters.AddWithValue("@flightInstanceId", _flightInstance.FlightInstanceId);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Seat tmp = new Seat();
                            tmp = BuildSeat(rdr);
                            seatList.Add(tmp);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return seatList;
        }
        //return a List<Seat> with all the Seat in the database
        public List<Seat> SelectAll()
        {
            List<Seat> seats = new List<Seat>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Seat";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Seat temSeat = new Seat();
                            temSeat = BuildSeat(rdr);
                            seats.Add(temSeat);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return seats;
        }
        //Returns a list on booked seats
        public List<Seat> FindBookedSeats(FlightInstance _flightInstance)
        {
            List<Seat> booked = new List<Seat>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "select fi.planeNo, seatNo from flightinstance fi join seatbooking sb on fi.flightInstanceId = sb.flightInstanceId where fi.flightinstanceId = @flightInstanceId";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@flightInstanceId", _flightInstance.FlightInstanceId);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Seat tempSeat = BuildSeat(rdr);
                            booked.Add(tempSeat);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return booked;
        }
       
    }
    
}
